SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.[DnnProduct_GetRolesByUser]
    
	@UserID        int,
	@PortalID      int

AS

SELECT dbo.DnnProduct_Roles.RoleName,
       dbo.DnnProduct_Roles.RoleId
	FROM dbo.DnnProduct_UserRoles
		INNER JOIN dbo.DnnProduct_Users on dbo.DnnProduct_UserRoles.UserId = dbo.DnnProduct_Users.UserId
		INNER JOIN dbo.DnnProduct_Roles on dbo.DnnProduct_UserRoles.RoleId = dbo.DnnProduct_Roles.RoleId
	WHERE  dbo.DnnProduct_Users.UserId = @UserID
		AND    dbo.DnnProduct_Roles.PortalId = @PortalID
		AND    (EffectiveDate <= getdate() or EffectiveDate is null)
		AND    (ExpiryDate >= getdate() or ExpiryDate is null)

GO
